// Copyright (C) 2025, ZWSOFT Co., LTD.(Guangzhou)
// Licensed under the MIT License. See LICENSE in the project root for license information.

namespace TestClient.Domain;
using Microsoft.Data.Sqlite;
using System;
using System.IO;
using System.Linq;
using System.Text;

/// <summary>
/// A utility class to generate a physical SQLite database file
/// from a DatabaseDefinition object.
/// </summary>
public static class DatabaseGenerator
{
    /// <summary>
    /// Creates and populates a SQLite database file based on the provided definition.
    /// If the file already exists, it will be deleted and recreated.
    /// </summary>
    /// <param name="definition">The object defining the database schema and data.</param>
    /// <param name="filePath">The path where the .sqlite file will be created.</param>
    public static void Generate(DatabaseDefinition definition, string filePath)
    {
        // Ensure a clean slate by deleting the old file if it exists.
        if (File.Exists(filePath))
        {
            File.Delete(filePath);
        }

        using var connection = new SqliteConnection($"Data Source={filePath}");
        connection.Open();

        foreach (var tableDef in definition.Tables)
        {
            // 1. Create the table schema
            var createTableSql = BuildCreateTableSql(tableDef);
            using (var command = connection.CreateCommand())
            {
                command.CommandText = createTableSql;
                command.ExecuteNonQuery();
            }

            // 2. Insert the data rows
            InsertData(connection, tableDef);
        }
    }

    private static string BuildCreateTableSql(TableDefinition tableDef)
    {
        var sqlBuilder = new StringBuilder();
        sqlBuilder.Append($"CREATE TABLE \"{tableDef.Name}\" (");

        var columnSqls = tableDef.Columns.Select(colDef =>
        {
            var colBuilder = new StringBuilder();
            colBuilder.Append($"\"{colDef.Name}\" {colDef.DataType}");
            if (colDef.IsPrimaryKey) colBuilder.Append(" PRIMARY KEY");
            if (colDef.IsNotNull) colBuilder.Append(" NOT NULL");
            if (colDef.DefaultValue != null)
            {
                colBuilder.Append($" DEFAULT {FormatDefaultValue(colDef.DefaultValue)}");
            }
            return colBuilder.ToString();
        });

        sqlBuilder.Append(string.Join(", ", columnSqls));
        sqlBuilder.Append(");");

        return sqlBuilder.ToString();
    }

    private static void InsertData(SqliteConnection connection, TableDefinition tableDef)
    {
        foreach (var row in tableDef.Rows)
        {
            var columnNames = string.Join(", ", row.Keys.Select(k => $"\"{k}\""));
            var parameterNames = string.Join(", ", row.Keys.Select(k => $"@{k}"));

            var insertSql = $"INSERT INTO \"{tableDef.Name}\" ({columnNames}) VALUES ({parameterNames});";

            using var command = connection.CreateCommand();
            command.CommandText = insertSql;

            foreach (var kvp in row)
            {
                // Use AddWithValue for simplicity. It correctly handles types.
                // DBNull.Value is used for SQL NULL.
                command.Parameters.AddWithValue($"@{kvp.Key}", kvp.Value ?? DBNull.Value);
            }
            command.ExecuteNonQuery();
        }
    }

    private static string FormatDefaultValue(object value)
    {
        // Quote strings, leave numbers as-is.
        if (value is string || value is char)
        {
            return $"'{value?.ToString()?.Replace("'", "''")}'"; // Escape single quotes
        }
        if (value is bool b)
        {
            return b ? "1" : "0"; // SQLite uses 1 and 0 for booleans
        }
        // For numbers, enums, etc.
        return value.ToString() ?? "";
    }
}
